<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class mdl_laporan_keuangan extends CI_Model {
    
	function __construct(){
        parent::__construct();
    }

    function get_pdf($tgl_awal, $tgl_akhir,$tipe){
    	$this->db->flush_cache();
    	$this->db->select('a.tgl_transaksi,a.kode_akun,a.debit,a.kredit,a.saldo,a.deskripsi,b.deskripsi as deskrip');
    	$this->db->from('transaksi_keuangan a');
    	$this->db->join('akun b', 'b.kode_akun = a.kode_akun');
    	$this->db->where("a.tgl_transaksi between'".$tgl_awal."' AND '".$tgl_akhir."'");

    	if($tipe == '1'){ // pemasukan
    		$this->db->like('a.kode_akun','1', 'after');
    	}else{
        	$this->db->like('a.kode_akun','2','after');
        }
   
    	// proses
	    return $this->db->get();
		
    }

    function getalltrans($tgl_awal, $tgl_akhir){
        $this->db->flush_cache();
        $this->db->select('a.tgl_transaksi,a.kode_akun,a.debit,a.kredit,a.saldo,a.deskripsi,b.deskripsi as deskrip');
        $this->db->from('transaksi_keuangan a');
        $this->db->join('akun b', 'b.kode_akun = a.kode_akun');
        $this->db->where("a.tgl_transaksi between'".$tgl_awal."' AND '".$tgl_akhir."'");

        // proses
        return $this->db->get();
        
    }

    function sumdebit($tgl_awal, $tgl_akhir){
    	$this->db->flush_cache();
    	$this->db->select('SUM(debit) as total');
    	$this->db->from('transaksi_keuangan');
    	//$this->db->join('akun b', 'b.kode_akun = a.kode_akun');
    	$this->db->where("tgl_transaksi between'".$tgl_awal."' AND '".$tgl_akhir."'");

    	$this->db->like('kode_akun','1', 'after');
    	$this->db->group_by('kredit');
   
    	$ret = $this->db->get();
    	// proses
		if($ret->num_rows()>0){
			return $ret->row()->total;
		}

	 	return 0;
		
    }

    function sumkredit($tgl_awal, $tgl_akhir){
    	$this->db->flush_cache();
    	$this->db->select('SUM(kredit) as total');
    	$this->db->from('transaksi_keuangan');
    	//$this->db->join('akun b', 'b.kode_akun = a.kode_akun');
    	$this->db->where("tgl_transaksi between'".$tgl_awal."' AND '".$tgl_akhir."'");

    	$this->db->like('kode_akun','2', 'after');
		$this->db->group_by('debit');   
    	
    	$ret = $this->db->get();
    	// proses
		if($ret->num_rows()>0){
			return $ret->row()->total;
		}

	 	return 0;
    }

    function get_pdf_lap($tgl_awal, $tgl_akhir){
    	$this->db->flush_cache();
    	$this->db->select('a.tgl_transaksi,a.kode_akun,a.debit,a.kredit,a.saldo,a.deskripsi,b.parent,b.deskripsi as deskrip');
    	$this->db->from('transaksi_keuangan a');
    	$this->db->join('akun b', 'b.kode_akun = a.kode_akun');
    	$this->db->where("a.tgl_transaksi between'".$tgl_awal."' AND '".$tgl_akhir."'");

   
    	// proses
	    return $this->db->get();
		
    }

    function get_pendapatan($tgl_awal,$tgl_akhir){
    	$this->db->flush_cache();
    	$this->db->select('a.deskripsi, SUM(b.debit) as debit',false);
    	$this->db->from('akun a');
    	$this->db->join('transaksi_keuangan b','b.kode_akun = a.kode_akun','left');
    	$this->db->where('a.tipe','P');
    	$this->db->where("b.tgl_transaksi between'".$tgl_awal."' AND '".$tgl_akhir."'");
    	$this->db->like('a.kode_akun','1','after');
    	$this->db->group_by('a.kode_akun');
    	return $this->db->get();
    }

    //foreach ($result as $key => $value) {
    	# code...

    function get_pengeluaran($tgl_awal,$tgl_akhir){
    	$this->db->flush_cache();
    	$this->db->select('a.deskripsi, SUM(b.kredit) as kredit',false);
    	$this->db->from('akun a');
    	$this->db->join('transaksi_keuangan b','b.kode_akun = a.kode_akun','left');
    	$this->db->where('a.tipe','P');
    	$this->db->where("b.tgl_transaksi between'".$tgl_awal."' AND '".$tgl_akhir."'");
    	$this->db->like('a.kode_akun','2','after');
    	$this->db->group_by('a.kode_akun');
    	return $this->db->get();
    }

    function getDataChartKeuangan($data){
        $i=0;
        foreach($data as $d){
            $this->db->flush_cache();
            $this->db->select("DATE_FORMAT(a.tgl_transaksi, '%M') AS bulan", FALSE);
            $this->db->from('transaksi_keuangan a');
            $this->db->where("DATE_FORMAT(a.tgl_transaksi,'%M')", $d);
            
            $tmp[$i] = $this->db->get()->num_rows();
            $i++;
        }
        return $tmp;
    }

    function getDataChartNeraca($data){
        $i=0;
        foreach($data as $d){
            $this->db->flush_cache();
            $this->db->select("DATE_FORMAT(tgl_transaksi, '%M') as bulan, SUM(debit) as total_debit, SUM(kredit) as total_kredit", FALSE);
            $this->db->from('transaksi_keuangan');
            $this->db->where("DATE_FORMAT(tgl_transaksi,'%M')", $d);
            $this->db->group_by('bulan');
        
            $res = $this->db->get();

            if($res->num_rows>0){
                $tmp[$i] = $res->row_array();
            }else{
                $tmp[$i]['bulan']= $d;
                $tmp[$i]['total_debit']= 0;
                $tmp[$i]['total_kredit']= 0;
            }
            $i++;
        }
        return $tmp;
    }

    function getDataGraph($kode_akun=''){
        $this->db->flush_cache();
        $this->db->select('*');
        $this->db->from('transaksi_keuangan');
        
        $this->db->like('kode_akun', '1','after');
        
        return $this->db->get();
    }
}
?>